package org.paradise.dms.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.paradise.dms.pojo.AllocationInfo;
import org.paradise.dms.pojo.Apartment;
import org.paradise.dms.pojo.Dormitory;
import org.paradise.dms.pojo.DormitoryRate;
import org.paradise.dms.pojo.Dormitoryconcretefunc;
import org.paradise.dms.pojo.StudentBedLink;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;

import com.dheaven.framework.dao.BaseHibernateDao;
import com.dheaven.framework.dao.DaoException;

/**
 * 
 * Description: 宿舍管理的DAO类
 * 
 * Copyright (c) 2008-2009 paraDise sTudio(DT). All Rights Reserved.
 * 
 * @version 1.0 2009-3-18 下午09:35:41 李双江（paradise.lsj@gmail.com）created
 */
@SuppressWarnings("unchecked")
@Service
public class DormitoryDAOImpl extends BaseHibernateDao {

	private static Logger log = Logger.getLogger(DormitoryDAOImpl.class);

	/**
	 * 
	 * Description: 根据公寓id列出所有该公寓的宿舍
	 * 
	 * @Version1.0 2009-3-19 下午07:39:20 李双江（paradise.lsj@gmail.com）创建
	 * @param apartmentid
	 * @return
	 * @throws DaoException
	 */
	public List<Dormitory> listAllDormitoryByApartmentID(String apartmentid) throws DaoException {
		List<Dormitory> list = null;
		list = this.find("from Dormitory where apartmentid = ? and dormitoryind=1 order by dormitorysort asc", new Object[] { apartmentid });
		return list;
	}

	/**
	 * 
	 * Description: 通过Dormitory对象插入一条公寓信息
	 * 
	 * @param dormitory
	 * @return
	 * @throws DaoException
	 */
	public String insertDormitory(Dormitory dormitory) {
		String statusMsg = "";
		try {
			this.create(dormitory);
			statusMsg = "创建宿舍成功";
			log.info("DMS_info：" + statusMsg);
			return statusMsg;

		} catch (DaoException e) {
			statusMsg = "创建宿舍失败";
			log.error("DMS_error:" + statusMsg);
			log.error("DMS_error：失败原因 " + e);
			return statusMsg;
		}
	}

	/**
	 * 
	 * Description:通过参数创建一条宿舍信息
	 * 
	 * @Version1.0 2009-4-3 下午09:46:30 李双江（paradise.lsj@gmail.com）创建
	 * @param dormitoryname
	 * @param dormitorylodgetype
	 * @param dormitorysex
	 * @param dormitoryfee
	 * @param apartmentname
	 * @param dormitoryfloorno
	 * @param dormdisplayname
	 * @param dormitoryfunc
	 * @return
	 * @throws DaoException
	 */
	public String insertDormitory(String jArray) throws DaoException {

		StringBuffer statusMsg = new StringBuffer("");
		String[] ja = jArray.split(",");
		for (int i = 0; i < ja.length; i++) {
			String[] jaa = ja[i].split("_");
			final String dormitoryname = jaa[0];
			final String dormitorylodgetype = jaa[1];
			final String dormitorysex = jaa[2];
			final String dormitoryfee = jaa[3];
			String apartmentname = jaa[4];
			final String dormitoryfloorno = jaa[5];
			final String dormdisplayname = jaa[6];
			final String dormitoryfunc = jaa[7];

			List<Apartment> apartList = this.find("from Apartment where apartmentname=? and apartmentind=1", new Object[] { apartmentname });

			if (apartList.size() == 0) {
				statusMsg.append(apartmentname + ":不存在");
				log.warn("DMS_warn:" + statusMsg.toString());
				break;
			}
			final String aptid = (String) ((Apartment) apartList.get(0)).getApartmentid();
			final String dormsort = aptid + String.format("%02d", Integer.parseInt(dormitoryfloorno)) + String.format("%03d", i);
			// log.info(dormsort + "==dormsort");

			List<Dormitory> dl = this.find("from Dormitory where dormitoryname = ? and apartmentid=?", new Object[] { dormitoryname, aptid });
			if (dl != null && dl.size() > 0) {
				// log.info(dormdisplayname + ":已经存在,");
				statusMsg.append(dormdisplayname + ":已经存在,");
				log.warn("DMS_warn:" + statusMsg.toString());
				continue;
			}
			statusMsg.append((String) this.getHibernateTemplate().execute(new HibernateCallback() {
				public String doInHibernate(Session session) {
					SQLQuery query = (SQLQuery) session.createSQLQuery("insert into dormitory (dormitoryname, dormitorylodgetype, dormitorysex, dormitoryfee, apartmentid, dormitoryfloorno, dormitorydisplayname,dormitoryfunction,dormitorysort) values (?, ?, ?, ?, ?, ?, ?,?,?)");
					query.setString(0, dormitoryname);
					query.setString(1, dormitorylodgetype);
					query.setString(2, dormitorysex);
					query.setInteger(3, Integer.parseInt(dormitoryfee));
					query.setInteger(4, Integer.parseInt(aptid));
					query.setInteger(5, Integer.parseInt(dormitoryfloorno));
					query.setString(6, dormdisplayname);
					query.setInteger(7, Integer.parseInt(dormitoryfunc));
					query.setInteger(8, Integer.parseInt(dormsort));

					if (query.executeUpdate() > 0) { // 插入成功
						Query dormquery = (Query) session.createQuery("from Dormitory where dormitoryname=:dormitoryname and apartmentid=:apartmentid");
						dormquery.setParameter("dormitoryname", dormitoryname);
						dormquery.setParameter("apartmentid", aptid);
						final int dormId = ((Dormitory) dormquery.list().get(0)).getDormitoryid();
						// log.info("宿舍的id是：==" + dormId);
						// :dormitoryid, :roomtype
						SQLQuery queryDormBed = (SQLQuery) session.createSQLQuery("call CreateDormitoryBed(?,?)");
						queryDormBed.setInteger(0, dormId);
						queryDormBed.setInteger(1, Integer.parseInt(dormitorylodgetype));
						if (queryDormBed.executeUpdate() > 0) {
							log.info("DMS_info:" + dormdisplayname + "创建床位成功");
						} else {
							log.error("DMS_error:" + dormdisplayname + "创建床位失败");
						}
						return dormdisplayname + ":宿舍创建成功\r\n";
					} else {
						log.error("DMS_error:" + dormdisplayname + "宿舍创建失败！");
						return dormdisplayname + ":宿舍创建失败\r\n";
					}
				}
			}));
		}
		
		//更新公寓totalrooms
		//由于暂时未想到取出公寓ID的好方法 所以此处更新了所有公寓的totalrooms。。
		this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session){
				SQLQuery query = (SQLQuery) session.createSQLQuery("update apartment a set a.apartmenttotalrooms=(select count(*) from dormitory d where d.apartmentid=a.apartmentid)");
				query.executeUpdate();
				
				return 0;
			}
		});
		
		log.info("DMS_info:" + " 创建宿舍的结果统计为 ：" + statusMsg);
		return statusMsg.toString();
	}

	/**
	 * 
	 * Description:返回宿舍总条数
	 * 
	 * @Version1.0 2009-3-19 下午11:42:51 李双江（paradise.lsj@gmail.com）创建
	 * @Version1.1 2010-1-26 郑旭 创建
	 * @return
	 * @throws DaoException
	 */
	public int getRows(String apartmentid, String floorno) throws DaoException {
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("select count(1)  from dormitory  do where do.apartmentid=? and do.dormitoryfloorno=? and do.dormitoryind=1 order by do.dormitoryname asc");
		query.setInteger(0, Integer.parseInt(apartmentid));
		query.setInteger(1, Integer.parseInt(floorno));
		return ((Number) query.uniqueResult()).intValue();
	}

	/**
	 * 
	 * Description:返回宿舍总条数
	 * 
	 * @Version1.0 2009-3-19 下午11:42:51 李双江（paradise.lsj@gmail.com）创建
	 * @return
	 * @throws DaoException
	 */
	public int getRows(String apartmentid) throws DaoException {
		List<Dormitory> list = this.find("FROM Dormitory do where do.apartmentid=? and do.dormitoryind=1", new Object[] { Integer.parseInt(apartmentid)});
		return list.size();
	}

	/**
	 * 
	 * Description: 根据公寓id和floorno查询dormitory，有分页
	 * 
	 * @Version1.0 2009-3-23 下午09:52:20 李双江（paradise.lsj@gmail.com）创建
	 * @Version1.1 2009-4-20 下午15:24:20
	 *             李双江（paradise.lsj@gmail.com）修改增加了按floorno查询条件
	 * @param apartmentid
	 * @param floorno
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	public List<Dormitory> listDormitoryByApartmentIDAndFloorNo(final String apartmentid, final String floorno, int pageSize, int startRow) {
		final int pageSize1 = pageSize;
		final int startRow1 = startRow;
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery("FROM Dormitory d where d.apartmentid=:apartmentid and d.dormitoryind=1 and d.dormitoryfloorno=:dormitoryfloorno order by d.dormitorysort asc");
				query.setFirstResult(startRow1);
				query.setMaxResults(pageSize1);
				query.setParameter("apartmentid", Integer.parseInt(apartmentid));
				query.setParameter("dormitoryfloorno", Integer.parseInt(floorno));
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 根据公寓id和floorno查询dormitory，有分页
	 * 
	 */
	public List listDormitoryByApartmentID(final String sql) {
		return this.getSession().createSQLQuery(sql).list();
	}

	/**
	 * 
	 * Description:为首页显示公寓重写的一个方法
	 * 
	 * @Version1.0 Apr 28, 2009 2:47:26 AM 李双江（paradise.lsj@gmail.com）创建
	 * @param apartmentid
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	public List<Dormitory> listDormitoryByApartID(final String apartmentid, int pageSize, int startRow) {
		final int pageSize1 = pageSize;
		final int startRow1 = startRow;
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery("FROM Dormitory d where d.apartmentid=:apartmentid and d.dormitoryind=1 order by d.dormitorysort asc");
				query.setFirstResult(startRow1);
				query.setMaxResults(pageSize1);
				query.setParameter("apartmentid", apartmentid);
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 根据公寓名加楼层号或者加宿舍号查询宿舍信息
	 * 
	 * @Version1.0 2009-3-20 上午03:21:26 李双江（paradise.lsj@gmail.com）创建
	 * @param apartmentname
	 * @param floorno
	 * @param dormitortname
	 * @param pageSize
	 * @param startRow
	 * @return
	 * @throws DaoException
	 */
	public List<Dormitory> getDormitoryByApartmentNameAndFloornoOrDormname(final String apartmentid, final String dormitoryfloorno, final String dormitoryname, int pageSize, int startRow) throws DaoException {
		final int pageSize1 = pageSize;
		final int startRow1 = startRow;
		final Apartment _apt = (Apartment) this.find("From Apartment where apartmentid=? and apartmentind=1", new Object[] { apartmentid }).get(0);
		final String _aptid = _apt.getApartmentid();
		// 进入这个方法的话，肯定apartmentname和floorno都有值了。否则可能去apartmentViewAction中
		// 现在只判断宿舍名称是否为空就可以了
		if (("".equals(dormitoryname) || dormitoryname == null)) { // 表示宿舍名称没有填写
			return this.getHibernateTemplate().executeFind(new HibernateCallback() {
				public List doInHibernate(Session session) throws HibernateException, SQLException {

					Query query; 
					if("0".equals(dormitoryfloorno)){
						query = session.createQuery("FROM Dormitory d  where d.apartmentid=:apartmentid and d.dormitoryind=1 order by d.dormitorysort asc");
					}else{
						query = session.createQuery("FROM Dormitory d  where d.apartmentid=:apartmentid and d.dormitoryfloorno=:dormitoryfloorno and d.dormitoryind=1 order by d.dormitorysort asc");
						query.setParameter("dormitoryfloorno", dormitoryfloorno);
					}
					query.setFirstResult(startRow1);
					query.setMaxResults(pageSize1);
					query.setParameter("apartmentid", _aptid);
					
					return query.list();
				}
			});
		} else {// 填写了宿舍名称
			if ("0".equals(dormitoryfloorno)) { // 直接是查询公寓楼和房间号
				return this.getHibernateTemplate().executeFind(new HibernateCallback() {
					public List doInHibernate(Session session) throws HibernateException, SQLException {
						Query query = session.createQuery("FROM Dormitory d  where d.apartmentid=:apartmentid and d.dormitoryname=:dormitoryname and d.dormitoryind=1 order by d.dormitorysort asc");
						query.setFirstResult(startRow1);
						query.setMaxResults(pageSize1);
						query.setParameter("apartmentid", _apt.getApartmentid());
						query.setParameter("dormitoryname", dormitoryname);
						return query.list();
					}
				});
			} else {
				return this.getHibernateTemplate().executeFind(new HibernateCallback() {
					public List doInHibernate(Session session) throws HibernateException, SQLException {
						Query query = session.createQuery("FROM Dormitory d  where d.apartmentid=:apartmentid and d.dormitoryfloorno=:dormitoryfloorno and d.dormitoryname=:dormitoryname and d.dormitoryind=1 order by d.dormitorysort asc");
						query.setFirstResult(startRow1);
						query.setMaxResults(pageSize1);
						query.setParameter("apartmentid", _apt.getApartmentid());
						query.setParameter("dormitoryfloorno", dormitoryfloorno);
						query.setParameter("dormitoryname", dormitoryname);
						return query.list();
					}
				});
			}
		}
	}

	/**
	 * 
	 * Description:调用存储过程获得宿舍显示图片的确定项（返回一个每个宿舍住多少人的list）
	 * 
	 * @Version1.0 2009-3-21 上午03:24:13 李双江（paradise.lsj@gmail.com）创建
	 * @param apartmentid
	 * @return
	 * @throws DaoException
	 */
	public List getDormitoryDisplayPictureSpec(String[] dormitoryid) throws DaoException {
		List<Object> lst = new ArrayList<Object>();
		Query q = this.getSession().createQuery("select count(sbl.studentbedlinkid) from StudentBedLink sbl where sbl.dormitoryid=:dormitoryid  group by sbl.dormitoryid");
		Long inStudentNum = 0l;
		for (int i = 0; i < dormitoryid.length; i++) {
			q.setParameter("dormitoryid", Integer.parseInt(dormitoryid[i]));
			// log.info(dormitoryid[i]);
			if (q.list() != null && q.list().size() > 0) { // 如果这个宿舍没有对应的床位记录返回的list可能大小为0
				// log.info("====" + q.list().get(0));
				inStudentNum = (Long) q.list().get(0);
			} else {
				inStudentNum = 0l; // 该宿舍为住人，返回住宿人数为0人
			}
			lst.add(inStudentNum); // 保证lst的大小是分页的大小
			// log.info(lst.size());
		}
		return lst;
	}

	/**
	 * 
	 * Description: 查询某个公寓某层的总房间数目，分页需要
	 * 
	 * @Version1.0 2009-3-20 上午03:42:06 李双江（paradise.lsj@gmail.com）创建
	 * @param apartmentid
	 * @param dormitoryfloorno
	 * @return
	 * @throws DaoException
	 * @throws DaoException
	 */
	public int getAFloorRows(String apartmentid, String dormitoryfloorno) throws DaoException {
		SQLQuery query ;
		if("0".equals(dormitoryfloorno)){
			 query = (SQLQuery) this.getSession().createSQLQuery("select count(dormitoryid) as totaldormnum from dormitory where apartmentid=? ");
			 query.setInteger(0, Integer.parseInt(apartmentid));
		}else{
			 query = (SQLQuery) this.getSession().createSQLQuery("select count(dormitoryid) as totaldormnum from dormitory where apartmentid=? and dormitoryfloorno=?");
			 query.setInteger(0, Integer.parseInt(apartmentid));
			 query.setInteger(1, Integer.parseInt(dormitoryfloorno));
		}
		int totaldormnum = ((Number) query.uniqueResult()).intValue();
		log.info("DMS_info: 公寓id为：" + apartmentid + dormitoryfloorno + "层" + "有" + totaldormnum + "间宿舍。");
		return totaldormnum;
	}

	/**
	 * 
	 * Description: 通过apartmentid和floorno来查看空闲的宿舍房态
	 * 
	 * @Version1.0 2009-3-23 上午01:04:41 李双江（paradise.lsj@gmail.com）创建
	 * @Version1.1 2009-5-5 02:50:43
	 *             李双江（paradise.lsj@gmail.com）增加了floorno参数，避免一次查询过多情况
	 * @param apartmentid
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	public List getAvailDormitoryByApartmentid(String apartmentid, String floorno,int studentdormitorytype, int pageSize, int startRow) {
		final int pageSize1 = pageSize;
		final int startRow1 = startRow;
		if (Integer.parseInt(floorno) == 0) { // 楼层参数没有选择
			SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("{call GetAvailDormByApartId(?,?,?)}").addScalar("dormitoryid", Hibernate.INTEGER).addScalar("inAmount", Hibernate.INTEGER).addScalar("dormitoryname", Hibernate.STRING).addScalar("dormitorylodgetype", Hibernate.INTEGER).addScalar("dormitorysex", Hibernate.INTEGER).addScalar("dname", Hibernate.STRING);
			query.setInteger(0, Integer.parseInt(apartmentid));
			query.setInteger(1, startRow1);
			query.setInteger(2, pageSize1);
			return query.list();
		} else {
			//System.out.println("-----------"+apartmentid+"@"+floorno+"@"+studentdormitorytype);
			SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("{call GetAvailDormByApartIdAndFloorNo(?,?,?,?,?)}").addScalar("dormitoryid", Hibernate.INTEGER).addScalar("inAmount", Hibernate.INTEGER).addScalar("dormitoryname", Hibernate.STRING).addScalar("dormitorylodgetype", Hibernate.INTEGER).addScalar("dormitorysex", Hibernate.INTEGER).addScalar("dname", Hibernate.STRING);
			query.setInteger(0, Integer.parseInt(apartmentid));
			query.setInteger(1, Integer.parseInt(floorno));
			query.setInteger(2, studentdormitorytype);
			query.setInteger(3, startRow1);
			query.setInteger(4, pageSize1);

			// log.info("query.list().size()为：" + query.list().size());
			// for (int i = 0; i < query.list().size(); i++) {
			// Object[] o = (Object[]) query.list().get(i);
			// int dormitoryid = (Integer) o[0];
			// int AvailAmount = (Integer) o[1];
			// String dormitoryname = (String) o[2];
			// int dormitorylodgetype = (Integer) o[3];
			// log.info("dormitoryid:\t" + dormitoryid + "\t" + "AvailAmount\t"
			// +
			// AvailAmount);
			// log.info("dormitoryname:\t" + dormitoryname + "\t" +
			// "dormitorylodgetype\t" + dormitorylodgetype);
			// }
			return query.list();
		}
	}

	/**
	 * 
	 * Description:通过preID查看宿舍的情况 （及时分配的时候需要通过preid去查询一个宿舍）
	 * 
	 * @Version1.0 Apr 22, 2009 3:12:54 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param dormpreid
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	public List listAvailDromPerPageByPreID(final String dormpreid, int pageSize, int startRow) {
		final int pageSize1 = pageSize;
		final int startRow1 = startRow;
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session) throws HibernateException, SQLException {
				SQLQuery query = session.createSQLQuery("{call GetAvailDormByPreId(?,?,?)}").addScalar("dormitoryid", Hibernate.INTEGER).addScalar("inAmount", Hibernate.INTEGER).addScalar("dormitoryName", Hibernate.STRING).addScalar("dormitorylodgetype", Hibernate.INTEGER).addScalar("dormitorysex", Hibernate.INTEGER);
				query.setInteger(0, Integer.parseInt(dormpreid));
				query.setInteger(1, startRow1);
				query.setInteger(2, pageSize1);
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 根据apartmentid和floorno返回所有空闲的宿舍记录数,totalRows
	 * 
	 * @Version1.0 2009-3-23 下午09:09:12 李双江（paradise.lsj@gmail.com）创建
	 * @param apartmentid
	 * @param floorno
	 * @return
	 */
	public int getTotalAvailDormRows(String apartmentid, String floorno,int studentdormitorytype) {
		if (Integer.parseInt(floorno) == 0) {  //貌似这里根本不起作用,因为单人分配页面限制了层号为必选
			SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("{call GetAvailDormTotalRowsByApartId(?)}").addScalar("dormitoryid", Hibernate.INTEGER).addScalar("AvailAmount", Hibernate.INTEGER).addScalar("dormitoryname", Hibernate.STRING).addScalar("dormitorylodgetype", Hibernate.INTEGER);
			query.setInteger(0, Integer.parseInt(apartmentid));
			return query.list().size();
		} else {
			//System.out.println("@@@@@@@@@@@@@@@"+apartmentid+"#"+floorno+"#"+studentdormitorytype);
			SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("{call GetAvailDormByApartIdWithoutPage(?,?,?)}").addScalar("dormitoryid", Hibernate.INTEGER).addScalar("AvailAmount", Hibernate.INTEGER).addScalar("dormitoryname", Hibernate.STRING).addScalar("dormitorylodgetype", Hibernate.INTEGER);
			query.setInteger(0, Integer.parseInt(apartmentid));
			query.setInteger(1, Integer.parseInt(floorno));
			query.setInteger(2,studentdormitorytype);
			
			return query.list().size();
		}
	}

	/**
	 * 
	 * Description:返回满足某个预分配标记id的总宿舍个数
	 * 
	 * @Version1.0 Apr 22, 2009 3:00:46 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param dormpreid
	 * @return
	 */
	public int getTotalAvailPreIdDormRows(String dormpreid) {
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("{call GetAvailDormAmountByPreId(?) }").addScalar("count(*)", Hibernate.INTEGER);
		query.setInteger(0, Integer.parseInt(dormpreid));
		return Integer.parseInt(query.list().get(0) + "");
	}

	/**
	 * 
	 * Description: 根据公寓id和宿舍名列出所有该公寓的指定名称的宿舍，用于StudentLodgeMgr
	 * 
	 * @Version1.1 2009-3-24 下午10:12:59 闫克 （kurtyan777@gmail.com） created
	 * @param apartmentid
	 * @param dormitoryname
	 *            optional
	 * @return
	 * @throws DaoException
	 */
	public List<Dormitory> getDormByApartIdAndDormName(String apartmentid, String dormitoryname,int studentdormitorytpye) throws DaoException {
		if(studentdormitorytpye == Dormitoryconcretefunc.DEFAULT_ID){
			return this.find("From Dormitory d where d.apartmentid = ? and d.dormitoryname = ? and d.dormitoryind=1", new Object[] { Integer.parseInt(apartmentid), dormitoryname }); 
		}
		return this.find("From Dormitory d where d.apartmentid = ? and d.dormitoryname = ? and dormitoryconcretefuncid=? and d.dormitoryind=1", new Object[] { Integer.parseInt(apartmentid), dormitoryname,studentdormitorytpye });
	}

	/**
	 * 
	 * Description: 根据宿舍id假删除一条宿舍
	 * 
	 * @Version1.0 2009-3-30 下午10:59:45 李双江（paradise.lsj@gmail.com）创建
	 * @param dormitoryid
	 * @return
	 */
	public String deleteDormitorybyDormitoryID(final String dormitoryid) {

		// 在这里删除一个宿舍的时候把这个宿舍所在的公寓的apartmenttotalrooms值减1
		// 注意string类型和int之间的转换
		String statusMsg = "";
		/**
		 * 判断要删除的宿舍是否为空
		 */
		List<StudentBedLink> bedlinklist = null;
		try {
			bedlinklist = this.find("from StudentBedLink where dormitoryid=?", new Object[] { Integer.parseInt(dormitoryid) });
		} catch (DaoException e1) {
			log.error("DMS_error：" + "查询id为" + dormitoryid + "的宿舍出现错误！");
			log.error("DMS_error：失败原因 " + e1);
		}
		if (bedlinklist.size() > 0) {
			log.warn("DMS_warn:宿舍不为空，无法删除！");
			statusMsg = "宿舍不为空，无法删除！";
			return statusMsg;
		}

		Dormitory dorm = null;
		Apartment apart = null;
		try {
			dorm = (Dormitory) this.find("from Dormitory where dormitoryid=?", new Object[] { dormitoryid }).get(0);
			dorm.setDormitoryind(0);

			apart = (Apartment) this.find("from Apartment where apartmentid=?", new Object[] { dorm.getApartmentid() }).get(0);
			int totalrooms = Integer.parseInt(apart.getApartmenttotalrooms());
			apart.setApartmenttotalrooms("" + (totalrooms--));
			this.update(dorm);
			this.update(apart);

			statusMsg = "删除宿舍成功！";
			log.info("DMS_info:删除宿舍id为：" + dormitoryid + "的宿舍成功！");
		} catch (DaoException e) {
			statusMsg = "宿舍信息查询有误！";
			log.error("DMS_error：" + "查询id为" + dormitoryid + "的宿舍出现错误！");
			log.error("DMS_error：失败原因 " + e);
		}
		return statusMsg;
	}

	/**
	 * 
	 * Description: 根据宿舍id查询宿舍信息
	 * 
	 * @Version1.0 2009-4-2 上午01:32:59 闫克 （kurtyan777@gmail.co) 创建
	 * @param dormitoryid
	 * @return
	 * @throws DaoException
	 */
	public List<Dormitory> getDormByDormId(int dormitoryid) throws DaoException {
		return this.find("from Dormitory d where d.dormitoryid = ?", new Object[] { dormitoryid });
	}

	/**
	 * 
	 * Description:获得所有的预分配标记的名称值
	 * 
	 * @Version1.0 2009-4-5 上午01:14:10 李双江（paradise.lsj@gmail.com）创建
	 * @return
	 */
	public List<AllocationInfo> getAllAllocationInfo() {
		return this.find("from AllocationInfo");
	}

	/**
	 * Description: 根据宿舍id删除其预分配标记
	 * 
	 * @Version1.0 2009-4-6 上午01:10:10 闫克（kurtyan777@gmail.com）创建
	 * @param dormitoryid
	 * @return Boolean
	 */
	public Boolean deleteDormitoryPreassignedId(final String dormitoryid) {
		return (Boolean) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Boolean doInHibernate(org.hibernate.Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("update dormitory d set d.allocationid=? where d.dormitoryid=?");
				query.setInteger(0, -1);
				query.setInteger(1, Integer.parseInt(dormitoryid));

				if (query.executeUpdate() > 0) {
					return true;
				}
				return false;
			}
		});
	}

	/**
	 * 
	 * Description: 分页查询预分配标记的宿舍总数，返回一个宿舍的list
	 * 
	 * @Version1.0 2009-4-5 下午12:59:00 李双江（paradise.lsj@gmail.com）创建
	 * @param dormitorypreallocationid
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	public List<Dormitory> getDormitoryPerPageByPreID(final String dormitorypreallocationid, final int pageSize, final int startRow) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s) throws HibernateException, SQLException {
				// String sqltosearch = "select * from dormitory where
				// allocationid=" + dormitorypreallocationid + " limit " +
				// startRow + "," + pageSize;
				SQLQuery query = (SQLQuery) s.createSQLQuery("select dormitory.dormitoryid,dormitory.allocationid,dormitory.apartmentid,dormitory.dormitorystatusid,dormitory.dormitoryname,dormitory.dormitorydisplayname,dormitory.dormitorysex,dormitory.dormitorytelephone,dormitory.dormitorylodgetype,dormitory.dormitoryfee,dormitory.dormitoryfunction,dormitory.dormitoryfloorno,dormitory.dormitorysort,dormitory.dormitorybednum,dormitory.dormitoryind, (dormitory.dormitorylodgetype-count(studentbedlink.dormitorybedid)) as avail, count(studentbedlink.dormitorybedid) from dormitory left join studentbedlink on dormitory.dormitoryid=studentbedlink.dormitoryid where dormitory.allocationid=? group by dormitory.dormitoryid order by dormitory.dormitorysort limit ?,?");
				query.setInteger(0, Integer.parseInt(dormitorypreallocationid));
				query.setInteger(1, startRow);
				query.setInteger(2, pageSize);
				/*
				 * Test返回值
				 */
				// log.info("Test宿舍Preid返回值====================================");
				// for (int m = 0; m < query.list().size(); m++) {
				// Object[] Obj = (Object[]) query.list().get(m);
				// for (int k = 0; k < Obj.length; k++) {
				// log.info(Obj[k] + "----这个是每项返回的结果！");
				// }
				// }
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 查询某一dormitorypreallocationid的宿舍的总行数
	 * 
	 * @Version1.0 2009-4-5 下午12:56:34 李双江（paradise.lsj@gmail.com）创建
	 * @param dormitorypreallocationid
	 * @return
	 */
	public int getDormitoryByDormitoryPreIDRows(String dormitorypreallocationid) {
		try {
			return this.find("from Dormitory where allocationid=?", new Object[] { dormitorypreallocationid }).size();
		} catch (DaoException e) {
			log.error(e);
			return 0;
		}
	}

	/**
	 * Description: 根据dormitoryid查询该宿舍可用床位
	 * 
	 * @version 1.0 2009-4-6 上午01:07:34 闫克（kurtyan777@gmail.com）创建
	 * @param dormitoryid
	 * @return
	 */
	public List getAvailBedByDormId(final String dormitoryid) {

		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("{call GetAvailBedByDormId(?)}").addScalar("dormitorybedid", Hibernate.INTEGER);
				query.setInteger(0, Integer.parseInt(dormitoryid));
				return query.list();
			}
		});

	}

	/**
	 * 
	 * Description: 根据dormitoryid返回该宿舍的入住学生列表
	 * 
	 * @Version1.0 2009-4-5 下午04:51:56 李双江（paradise.lsj@gmail.com）创建
	 * @param dormitoryid
	 * @return
	 */
	public List getStudentInfoByDormitoryID(final String dormitoryid) {
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createSQLQuery("{call GetStudentinfoByDormitoryId(?)}");
				query.setParameter(0, Integer.parseInt(dormitoryid));
				// log.info("getStudentInfoByDormitoryID --查出来的size是==" +
				// query.list().size());
				// for (int i = 0; i < query.list().size(); i++) {
				// Object[] o = (Object[]) query.list().get(i);
				// Integer a1 = (Integer) o[0];
				// Integer a2 = (Integer) o[1];
				// String a3 = (String) o[6]; // 姓名
				// String a4 = (String) o[7]; // 性别
				// String a5 = (String) o[27]; // 学院信息名
				// String a6 = (String) o[28]; // 专业信息名
				// log.info("###a1:\t" + a1 + "\t" + "###a2\t" + a2);
				// log.info("%%a3:\t" + a3 + "\t" + "%%a4\t" + a4);
				// log.info("%%a5:\t" + a5 + "\t" + "%%a6\t" + a6);
				// }
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description:根据传入的dormitoryid的列表和预分配的id值批量更新宿舍为某个预分配标记
	 * 
	 * @Version1.0 2009-4-6 下午02:08:17 李双江（paradise.lsj@gmail.com）创建
	 * @param dormidList
	 * @param dormitoryallocationid
	 * @return
	 */
	public String updateBatchDormitoryPreassignInfo(final String dormidList, final String dormitoryallocationid) {
		final String[] dl = dormidList.split(",");
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("update dormitory set allocationid=? where dormitoryid=?");
		for (int i = 0; i < dl.length; i++) {
			query.setParameter(0, dormitoryallocationid);
			query.setParameter(1, dl[i]);
			query.executeUpdate();
		}
		return "更新宿舍预分配状态成功！";
	}

	/**
	 * 
	 * Description: 宿舍批量更新页面批量标记整个公寓的preid方法
	 * 
	 * @Version1.0 Apr 29, 2009 12:27:13 AM 李双江（paradise.lsj@gmail.com）创建
	 * @param apartmentid
	 * @param dormallocationid
	 * @return
	 */
	public String updateAApartmentDormPreID(String apartmentid, String dormallocationid) {
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("update dormitory set allocationid=? where apartmentid=? and dormitoryind=1");
		query.setParameter(0, dormallocationid);
		query.setParameter(1, apartmentid);
		int result = query.executeUpdate();
		if (result > 0) {
			log.error("DMS_info:批量更新公寓id为" + apartmentid + "的宿舍预分配标记值为" + dormallocationid + "成功");
			return "批量更新成功";
		}
		log.error("DMS_error:批量更新公寓id为" + apartmentid + "的宿舍预分配标记值为" + dormallocationid + "失败");
		return "批量更新失败";
	}

	/**
	 * Description: 根据宿舍id、待插入床位数目插入相应数目的床位 此方法用于房型更改功能
	 * 
	 * @Version1.0 2009-4-21 下午23:21:17 闫克（kurtyan777@gmail.com）创建
	 * @param dormitoryid
	 * @param bedamount
	 * @return 20 - "创建床位失败" 21 - "创建床位成功"
	 */
	public int insertDormitoryBed(final String dormitoryid, final int bedamount) {
		return (Integer) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Integer doInHibernate(Session session) {
				SQLQuery queryDormBed = (SQLQuery) session.createSQLQuery("call CreateDormitoryBed(?,?)");
				queryDormBed.setInteger(0, Integer.parseInt(dormitoryid));
				queryDormBed.setInteger(1, bedamount);
				if (queryDormBed.executeUpdate() > 0) {
					log.info("DMS_info:更改房型 - 插入新床位成功");
					return 21;
				} else {
					log.error("DMS_error:更改房型 - 插入新床位失败");
					return 20;
				}
			}
		});
	}

	/**
	 * Description: 根据宿舍id、待删除床位数目删除相应数目的床位 此方法用于房型更改功能
	 * 
	 * 
	 * @Version1.0 2009-4-21 下午23:21:17 闫克（kurtyan777@gmail.com）创建
	 * @param dormitoryid
	 * @param bedamount
	 * @return 10 - "宿舍已住人数大于新房型" 11 - "删除床位成功"
	 */
	public int deleteDormitoryBed(final String dormitoryid, final int bedamount) {
		return (Integer) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Integer doInHibernate(Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("select dormitorybed.dormitorybedid,dormitorybed.dormitoryid from dormitorybed left join studentbedlink on dormitorybed.dormitoryid=studentbedlink.dormitoryid where dormitorybed.dormitoryid=? and studentbedlink.dormitoryid is null");
				query.setInteger(0, Integer.parseInt(dormitoryid));
				List bedlist = query.list();
				// bedlist只有一列 其行数为宿舍空床位数

				if (bedlist.size() < bedamount) {
					log.warn("DMS_warn:宿舍已住人数大于新房型可住人数，无法更改房型");
					return 10;
				}

				SQLQuery queryDelBed = (SQLQuery) session.createSQLQuery("delete dormitorybed from dormitorybed where dormitorybedid=?");
				for (int i = 0; i < bedamount; i++) {
					Object[] obj = (Object[]) bedlist.get(i);
					System.out.println(obj.length);
					queryDelBed.setInteger(0, Integer.parseInt(obj[0] + ""));
					queryDelBed.executeUpdate();
				}
				log.warn("DMS_warn:更改房型 - 删除床位成功");
				return 11;

			}
		});
	}

	/**
	 * Description: 根据apartid查询公寓内所有宿舍
	 * 
	 * @version 1.0 2009-5-5 上午 00:02:59 闫克 （kurtyan777@gmail.com） 创建
	 * @param apartid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List getDormListByApartId(final String apartid) {
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(org.hibernate.Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("select dormitoryid, dormitorydisplayname from dormitory where apartmentid=? and dormitoryind=1 order by dormitorysort");
				query.setInteger(0, Integer.parseInt(apartid));
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 根据学号查询宿舍卫生状况
	 * 
	 * @Version1.0 May 9, 2009 6:38:39 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param stuno
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List getDormitoryRateByStudentno(final String stuno,int studentdormitorytype) {
				
				String sql = "select dormitoryrate.dormitoryrateid,dormitoryrate.dormitoryid,dormitoryrate.dormitoryscore,dormitoryrate.dormitoryratedate,dormitoryrate.dormitoryrateperson,dormitoryrate.dormtioryratemisc from  dormitoryrate left join studentbedlink on studentbedlink.dormitoryid = dormitoryrate.dormitoryid left join student on student.studentid = studentbedlink.studentid where student.studentno = ?";
				if(studentdormitorytype != -1){
					sql += " and student.studentdormitorytype=?";
				}
				SQLQuery query = this.getSession().createSQLQuery(sql);
				query.setString(0, stuno);
				if(studentdormitorytype != -1){
					query.setInteger(1, studentdormitorytype);
				}
				return query.list();
	}

	/**
	 * 
	 * Description:
	 * 
	 * @Version1.0 May 10, 2009 4:28:08 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param dr
	 */
	public void insertDormitoryRateFromEXCEL(DormitoryRate dr) {
		try {
			this.create(dr);
		} catch (DaoException e) {
			log.error("DMS_error:插入宿舍卫生信息记录出错！");
			log.error("错误原因" + e);
		}
	}

	/**
	 * 
	 * Description: 返回满足特定dormitorypreid的dormitory列表
	 * 
	 * @Version1.0 May 20, 2009 10:11:16 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param dormitorypreid
	 * @return
	 */
	public String[][] getDormitoryIDListByDormitoryPreID(String dormitorypreid) {
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("select dormitoryid,dormitorydisplayname,dormitoryfee from dormitory where dormitoryind=1 and allocationid=?");
		query.setInteger(0, Integer.parseInt(dormitorypreid));
		List list = query.list();
		if (list != null && list.size() > 0) {
			String[][] str = new String[list.size()][3];
			for (int i = 0; i < list.size(); i++) {
				Object[] o = (Object[]) list.get(i);
				str[i][0] = o[0] + "";
				str[i][1] = o[1] + "";
				str[i][2] = o[2] + "";
			}
			return str;
		}
		return null;
	}

	/**
	 * 
	 * Description: 返回所有宿舍的预分配id为dormitorypreid的宿舍的总可住人数
	 * 
	 * @Version1.0 May 21, 2009 11:24:29 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param dormitorypreid
	 * @return
	 */
	public int getAvailDormInAmountByDormitoryPreID(final String dormitorypreid) {
		return (Integer) this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session){
				SQLQuery query = (SQLQuery) session.createSQLQuery("select count(*) from availdormwithpreid where allocationid=?");
				query.setInteger(0, Integer.parseInt(dormitorypreid));
				
				//如果preid不存在的话，sql语句返回值为0
				return Integer.parseInt(query.list().get(0) + "");
			}
		});
	}
	
	public List advancedDormSearch(String sql){
		return this.getSession().createSQLQuery(sql).list();
	}
	public int getAdvancedDormSearchTotalNumber(String sql){
		return this.getSession().createSQLQuery(sql).list().size();
	}
	//保存在dormsearchviewer.jsp页面编辑的dorm信息
	public String updateDormInfo(String fieldname ,String value,String id ){
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("update dormitory set "+fieldname + " =? where dormitoryid = "+id+" and dormitoryind=1");
		query.setParameter(0, value);
		int result = query.executeUpdate();
		if (result > 0) {
			log.error("DMS_info:更新id为" + id + "的宿舍" + fieldname + "字段值为"+value+"成功");
			return "success";
		}
		log.error("DMS_error:更新id为" + id + "的宿舍" + fieldname+ "字段值为"+value+"失败");
		return "failure";
		
	}
	
	
	//dormsearchviewer.jsp页面用到的获得宿舍所有具体功能的名称
	public String[][] getAllDormitoryConcreteFunction(){
		List list = this.getSession().createSQLQuery("select * from dormitoryconcretefunc").list();
		if (list != null && list.size() > 0) {
			String[][] str = new String[list.size()][4];
			for (int i = 0; i < list.size(); i++) {
				Object[] o = (Object[]) list.get(i);
				str[i][0] = o[0] + "";
				str[i][1] = o[1] + "";
				str[i][2] = o[2] + "";
				str[i][3] = o[3] + "";
			}
			return str;
		}
		return null;
	}

}
